*Set directory to the Replicationgovernance\finkelextension folder

cd "XXX\ReplicationGovernance\Finkelextension"


******************Regional/subregional aid needs to be calculated from the region lines in the explorer data.  We will do their aggregation technique (divide by N countries in region as if they're equally distributed)*******************

*region calculations average amount.  Full data will be full rank, so I will need to merge this data in and then zero out other data except for regional data.  

*bring in USAID data that is that has data on aid and such

import delimited "us_foreign_aid_completecsv.csv", encoding(UTF-8) 

*drop obligations, keep only disbursements---which we have starting in 2001
drop if transaction_type_name=="Obligations"

*destring fiscal_year, which used to have some typos that made it string.
destring fiscal_year, replace

drop if fiscal_year<2001

drop if fiscal_year>2018

*start with non-USAID aid
*create nonUSAID variable
*create indicator for all non-USAIDcategories

gen nonUSAID=1

*categories that are USAID.  Only USAID in this data.

replace nonUSAID=0 if implementing_agency_id==1

*Sum data for country by year that are democracy and governance (AID_100)---governance codes in variable USG_sector_name are: 

*Democracy, Human Rights and Governance

gen dgaid = 0
replace dgaid=1 if usg_sector_id==46

*Civil Society

replace dgaid=1 if usg_sector_id==10

*Good governance

replace dgaid=1 if usg_sector_id==8

*Political competition and consensus

replace dgaid=1 if usg_sector_id==9

*Rule of law and human rights

replace dgaid=1 if usg_sector_id==7

*sum up these categories, USAID dg aid (~ AID_100)

sort fiscal_year country_id
by fiscal_year country_id: egen USAIDdgconstant2016=sum(constant_amount) if nonUSAID==0 & dgaid==1 

*Fill non-zero values with the maximum value to prepare for collapse to country-year unit
replace USAIDdgconstant2016=0 if USAIDdgconstant2016==.
by fiscal_year country_id: egen USAIDdgconstant2016repeat = max(USAIDdgconstant2016)

*Sum USAID data for country by year that are not governance (~ AID_000)

sort fiscal_year country_id
by fiscal_year country_id: egen USAIDnondgconstant2016=sum(constant_amount) if nonUSAID==0 & dgaid==0

*Fill non-zero values with the maximum value to prepare for collapse to country-year unit
replace USAIDnondgconstant2016=0 if USAIDnondgconstant2016==.
by fiscal_year country_id: egen USAIDnondgconstant2016repeat = max(USAIDnondgconstant2016)

*Sum data across all US agencies and projects that are not USAID per year.  (~ Variable AID_2+state dept aid)
sort fiscal_year country_id
by fiscal_year country_id: egen nonUSAIDconstant2016=sum(constant_amount) if nonUSAID==1

*Fill non-zero values with the maximum value to prepare for collapse to country-year unit
replace nonUSAIDconstant2016=0 if nonUSAIDconstant2016==.
by fiscal_year country_id: egen nonUSAIDconstant2016repeat = max(nonUSAIDconstant2016)

*****clean up

*generate tag for single observation to keep for each country_id

egen singlecountryyear=tag(fiscal_year country_id)

*drop regional aid information, will be used in a different more complicated process to generate variables.

*there's simply no way to make "world" disbursed aid types useful for this analysis.

drop if country_id==1002

*Nor "Western Hemisphere"

drop if country_id==1035

*Keep single country year observations

keep if singlecountryyear==1

*keep only variables which are at country year level.

keep country_id country_code country_name region_id region_name income_group_id income_group_name USAIDdgconstant2016repeat USAIDnondgconstant2016repeat nonUSAIDconstant2016repeat fiscal_year

*drop countries---those are dealt with in other file

drop if country_id<1000

*get rid of several non-countries in the region area, drop Kosovo

drop if country_id==1071

*drop Pacific Island Trust

drop if country_id==1060

*drop Dutch Antilles

drop if country_id==1072

*drop Tibet

drop if country_id==1063

*generate vertical column variables that reflect each country observation.  Need regional DG and non-DG USAID figures

*Generate regional USAID DG separate variable by year... might need to be 0 vs missing?

gen region1005dg=.

replace region1005dg=USAIDdgconstant2016repeat if country_id==1005

gen region1006dg=.

replace region1006dg=USAIDdgconstant2016repeat if country_id==1006

gen region1007dg=.

replace region1007dg=USAIDdgconstant2016repeat if country_id==1007

gen region1008dg=.

replace region1008dg=USAIDdgconstant2016repeat if country_id==1008

gen region1009dg=.

replace region1009dg=USAIDdgconstant2016repeat if country_id==1009

gen region1010dg=.

replace region1010dg=USAIDdgconstant2016repeat if country_id==1010

gen region1011dg=.

replace region1011dg=USAIDdgconstant2016repeat if country_id==1011

gen region1012dg=.

replace region1012dg=USAIDdgconstant2016repeat if country_id==1012

gen region1013dg=.

replace region1013dg=USAIDdgconstant2016repeat if country_id==1013

gen region1014dg=.

replace region1014dg=USAIDdgconstant2016repeat if country_id==1014

gen region1015dg=.

replace region1015dg=USAIDdgconstant2016repeat if country_id==1015

gen region1016dg=.

replace region1016dg=USAIDdgconstant2016repeat if country_id==1016

gen region1017dg=.

replace region1017dg=USAIDdgconstant2016repeat if country_id==1017

gen region1018dg=.

replace region1018dg=USAIDdgconstant2016repeat if country_id==1018

gen region1019dg=.

replace region1019dg=USAIDdgconstant2016repeat if country_id==1019

gen region1020dg=.

replace region1020dg=USAIDdgconstant2016repeat if country_id==1020

gen region1021dg=.

replace region1021dg=USAIDdgconstant2016repeat if country_id==1021

gen region1022dg=.

replace region1022dg=USAIDdgconstant2016repeat if country_id==1022

gen region1023dg=.

replace region1023dg=USAIDdgconstant2016repeat if country_id==1023

gen region1024dg=.

replace region1024dg=USAIDdgconstant2016repeat if country_id==1024

gen region1025dg=.

replace region1025dg=USAIDdgconstant2016repeat if country_id==1025

gen region1026dg=.

replace region1026dg=USAIDdgconstant2016repeat if country_id==1026

gen region1027dg=.

replace region1027dg=USAIDdgconstant2016repeat if country_id==1027

gen region1028dg=.

replace region1028dg=USAIDdgconstant2016repeat if country_id==1028

gen region1029dg=.

replace region1029dg=USAIDdgconstant2016repeat if country_id==1029

gen region1030dg=.

replace region1030dg=USAIDdgconstant2016repeat if country_id==1030

gen region1031dg=.

replace region1031dg=USAIDdgconstant2016repeat if country_id==1031

gen region1032dg=.

replace region1032dg=USAIDdgconstant2016repeat if country_id==1032

gen region1033dg=.

replace region1033dg=USAIDdgconstant2016repeat if country_id==1033

gen region1034dg=.

replace region1034dg=USAIDdgconstant2016repeat if country_id==1034

gen region1036dg=.

replace region1036dg=USAIDdgconstant2016repeat if country_id==1036

*generate non-DG USAID regional data

gen region1005nondg=.

replace region1005nondg=USAIDnondgconstant2016repeat if country_id==1005

gen region1006nondg=.

replace region1006nondg=USAIDnondgconstant2016repeat if country_id==1006

gen region1007nondg=.

replace region1007nondg=USAIDnondgconstant2016repeat if country_id==1007

gen region1008nondg=.

replace region1008nondg=USAIDnondgconstant2016repeat if country_id==1008

gen region1009nondg=.

replace region1009nondg=USAIDnondgconstant2016repeat if country_id==1009

gen region1010nondg=.

replace region1010nondg=USAIDnondgconstant2016repeat if country_id==1010

gen region1011nondg=.

replace region1011nondg=USAIDnondgconstant2016repeat if country_id==1011

gen region1012nondg=.

replace region1012nondg=USAIDnondgconstant2016repeat if country_id==1012

gen region1013nondg=.

replace region1013nondg=USAIDnondgconstant2016repeat if country_id==1013

gen region1014nondg=.

replace region1014nondg=USAIDnondgconstant2016repeat if country_id==1014

gen region1015nondg=.

replace region1015nondg=USAIDnondgconstant2016repeat if country_id==1015

gen region1016nondg=.

replace region1016nondg=USAIDnondgconstant2016repeat if country_id==1016

gen region1017nondg=.

replace region1017nondg=USAIDnondgconstant2016repeat if country_id==1017

gen region1018nondg=.

replace region1018nondg=USAIDnondgconstant2016repeat if country_id==1018

gen region1019nondg=.

replace region1019nondg=USAIDnondgconstant2016repeat if country_id==1019

gen region1020nondg=.

replace region1020nondg=USAIDnondgconstant2016repeat if country_id==1020

gen region1021nondg=.

replace region1021nondg=USAIDnondgconstant2016repeat if country_id==1021

gen region1022nondg=.

replace region1022nondg=USAIDnondgconstant2016repeat if country_id==1022

gen region1023nondg=.

replace region1023nondg=USAIDnondgconstant2016repeat if country_id==1023

gen region1024nondg=.

replace region1024nondg=USAIDnondgconstant2016repeat if country_id==1024

gen region1025nondg=.

replace region1025nondg=USAIDnondgconstant2016repeat if country_id==1025

gen region1026nondg=.

replace region1026nondg=USAIDnondgconstant2016repeat if country_id==1026

gen region1027nondg=.

replace region1027nondg=USAIDnondgconstant2016repeat if country_id==1027

gen region1028nondg=.

replace region1028nondg=USAIDnondgconstant2016repeat if country_id==1028

gen region1029nondg=.

replace region1029nondg=USAIDnondgconstant2016repeat if country_id==1029

gen region1030nondg=.

replace region1030nondg=USAIDnondgconstant2016repeat if country_id==1030

gen region1031nondg=.

replace region1031nondg=USAIDnondgconstant2016repeat if country_id==1031

gen region1032nondg=.

replace region1032nondg=USAIDnondgconstant2016repeat if country_id==1032

gen region1033nondg=.

replace region1033nondg=USAIDnondgconstant2016repeat if country_id==1033

gen region1034nondg=.

replace region1034nondg=USAIDnondgconstant2016repeat if country_id==1034

gen region1036nondg=.

replace region1036nondg=USAIDnondgconstant2016repeat if country_id==1036

*Generate number of countries in each region, will also list them on commented line for future use.  Used USAID explorer to try to find data on country/region composition: https://explorer.usaid.gov/about#tab-glossary but this provides limited guidance.  UN subregions used after that, but not overly helpful in Europe especially.  Eurasian area definitions did not fit with any UN classification neatly, so I ended up using broad strokes more akin to the cold war in terms of splitting western/central/eastern europe up---closer to CIA factbook definitions.

*Asia, Middle East, and North Africa Region
*Asia+Middle East and North Africa categories 41+19=60: Burma, Brunei, Hong Kong, China (Tibet), Cambodia, China (PRC), Taiwan, Indonesia, Japan, Korea Republic, Korea Dem Republic, Laos, Macau, Malaysia, Mongolia, Phillipines, Singapore, Thailand, Timor-Leste, Vietnam, Afghanistan, Bangladesh, Bhutan, India, Kazakhstan, Kyrgyzstan, Maldives, Nepal, Pakistan, Sri Lanka, Tajikistan, Turkmenistan, Uzbekistan, Armenia, Azerbaijan, Belarus, Georgia, Moldova, Russia, Turkey, Ukraine Morocco, Algeria, Tunisia, Libya, Egypt, Israel, West Bank/Gaza, Lebanon, Syria, Jordan, Saudi Arabia, Yemen, Oman, UAE, Qatar, Kuwait, Iraq, Iran, Bahrain

gen region1005numcountries=60

*Asia Region
*20+13+8+15=56 countries: Burma, Brunei, Hong Kong, China (Tibet), Cambodia, China (PRC), Taiwan, Indonesia, Japan, Korea Republic, Korea Dem Republic, Laos, Macau, Malaysia, Mongolia, Phillipines, Singapore, Thailand, Timor-Leste, Vietnam, Afghanistan, Bangladesh, Bhutan, India, Kazakhstan, Kyrgyzstan, Maldives, Nepal, Pakistan, Sri Lanka, Tajikistan, Turkmenistan, Uzbekistan, Armenia, Azerbaijan, Belarus, Georgia, Moldova, Russia, Turkey, Ukraine, Egypt, Israel, West Bank/Gaza, Lebanon, Syria, Jordan, Saudi Arabia, Yemen, Oman, UAE, Qatar, Kuwait, Iraq, Iran, Bahrain

gen region1006numcountries=56

*Central America Region
*7 countries: Belize, Costa Rica, El Salvador, Guatemala, Honduras, Nicaragua, Panama

gen region1007numcountries=7

*Central America and Carribean region
*29 countries: Belize, Costa Rica, El Salvador, Guatemala, Honduras, Nicaragua, Panama, Anguilla, Antigua and Barbuda, Aruba, Bahamas, Barbados, British Virgin Islands, Cayman Islands, Cuba, ,Curacao, Dominica, Dominican Republic, Grenada, Haiti, Jamaica, Martinique, Montserrat, Dutch Antilles, St Kitts and Nevis, St. Lucia, St Vincent and the Grenadines, Trinidad and Tobago, Turks and Caicos

gen region1008numcountries=29

*Central and Eastern European region---CEE def
*19 countries: Albania, Bosnia and Herzegovina, Bulgaria, Croatia, Cyprus, Czechia, Estonia, Finland, Hungary, Kosovo, Latvia, Lithuania, Macedonia, Moldova, Poland, Romania, Serbia, Slovak Republic, Slovenia

gen region1009numcountries=19

*Carribean Region
*22 countries: Anguilla, Antigua and Barbuda, Aruba, Bahamas, Barbados, British Virgin Islands, Cayman Islands, Cuba, ,Curacao, Dominica, Dominican Republic, Grenada, Haiti, Jamaica, Martinique, Montserrat, Dutch Antilles, St Kitts and Nevis, St. Lucia, St Vincent and the Grenadines, Trinidad and Tobago, Turks and Caicos

gen region1010numcountries=22

*Eastern Africa
*21 countries: Burundi, British Indian Ocean Territory, Comoros, Djibouti, Eritrea, Ethiopia, Kenya, Madagascar, Malawi, Mauritius, Mayotte, Mozambique, Rwanda, Seychelles, Somalia, South Sudan, Sudan, Tanzania, Uganda, Zambia, Zimbabwe
gen region1011numcountries=21

*East Asia and Oceania
*39 countries: Australia, Brunei, Burma (Myanmar), Cambodia, China (P.R. Hong Kong), China (P.R.C.), China (Tibet), China, Republic of (Taiwan), Cook Islands, Fiji, French Polynesia, Indonesia, Japan, Kiribati, Korea Republic, Korea--Democratic Republic, Laos, Macau, Malaysia, Marshall Islands, Micronesia (Federated States), Mongolia, Nauru, New Caledonia, New Zealand, Pacific Island Trust Territory, Palau, Papua New Guinea, Philippines, Samoa, Singapore, Solomon Islands, Thailand, Timor-Leste, Tonga, Tuvalu, Vanuatu, Vietnam

gen region1012numcountries=39

*Eastern and Central Africa region
*30 countries: Burundi, British Indian Ocean Territory, Comoros, Djibouti, Eritrea, Ethiopia, Kenya, Madagascar, Malawi, Mauritius, Mayotte, Mozambique, Rwanda, Seychelles, Somalia, South Sudan, Sudan, Tanzania, Uganda, Zambia, Zimbabwe, Angola, Cameroon, Central African Republic, Chad, DRC, Rep Congo, Equatorial Guinea, Gabon, Sao Tome and Principe
gen region1013numcountries=30

*Eastern Europe 
*9 countries: Bulgaria, Czechia, Estonia, Hungary, Latvia, Lithuania, Poland, Romania, Slovak Republic
gen region1014numcountries=9

*Europe and Eurasia
*45 countries: Albania, Armenia, Austria, Azerbaijan, Belarus, Belgium, Bosnia and Herzegovina, Bulgaria, Croatia, Cyprus, Czechia, Denmark, Estonia, Finland, France, Georgia, Germany, Greece, Hungary, Iceland, Ireland, Italy, Kosovo, Latvia, Lithuania, Luxembourg, Macedonia, Malta, Moldova, Montenegro, Netherlands, Norway, Poland, Portugal, Romania, Russia, Serbia, Slovak Republic, Slovenia, Spain, Sweden, Switzerland, Turkey, Ukraine, United Kingdom

gen region1015numcountries=45

*Eastern Asia
*8 countries: China, Hong Kong, Taiwan, Tibet, Macao, Mongolia, Korea Dem Rep, Korean Rep, Japan
gen region1016numcountries=8

*East and South Africa
*26 countries: Burundi, British Indian Ocean Territory, Comoros, Djibouti, Eritrea, Ethiopia, Kenya, Madagascar, Malawi, Mauritius, Mayotte, Mozambique, Rwanda, Seychelles, Somalia, South Sudan, Sudan, Tanzania, Uganda, Zambia, Zimbabwe, Botswana, Eswatini, Lesotho, Namibia, South Africa

gen region1017numcountries=26

*Europe
*42 countries: Albania, Austria, Belarus, Belgium, Bosnia and Herzegovina, Bulgaria, Croatia, Cyprus, Czechia, Denmark, Estonia, Finland, France, Germany, Greece, Hungary, Iceland, Ireland, Italy, Kosovo, Latvia, Lithuania, Luxembourg, Macedonia, Malta, Moldova, Montenegro, Netherlands, Norway, Poland, Portugal, Romania, Russia, Serbia, Slovak Republic, Slovenia, Spain, Sweden, Switzerland, Turkey, Ukraine, United Kingdom

gen region1018numcountries=42

*Eurasia region 
*8 countries: Armenia, Azerbaijan, Belarus, Georgia, Moldova, Russia, Turkey, Ukraine

gen region1019numcountries=8

*Latin America Region
*21 countries Belize, Costa Rica, El Salvador, Guatemala, Honduras, Nicaragua, Panama, Argentina, Bolivia, Brazil, Chile, Colombia, Ecuador, French Guiana, Guyana, Paraguay, Peru, Suriname, Uruguay, Venezuela, Mexico

gen region1020numcountries=21

*Latin America and Carribean region
*43 countries Belize, Costa Rica, El Salvador, Guatemala, Honduras, Nicaragua, Panama, Argentina, Bolivia, Brazil, Chile, Colombia, Ecuador, French Guiana, Guyana, Paraguay, Peru, Suriname, Uruguay, Venezuela, Anguilla, Antigua and Barbuda, Aruba, Bahamas, Barbados, British Virgin Islands, Cayman Islands, Cuba, , Curacao, Dominica, Dominican Republic, Grenada, Haiti, Jamaica, Martinique, Montserrat, Dutch Antilles, St Kitts and Nevis, St. Lucia, St Vincent and the Grenadines, Trinidad and Tobago, Turks and Caicos, Mexico

gen region1021numcountries=43

*Middle East region
*Middle East: 15 countries, Israel, West Bank/Gaza, Lebanon, Syria, Jordan, Saudi Arabia, Yemen, Oman, UAE, Qatar, Kuwait, Iraq, Iran, Bahrain, Egypt

gen region1022numcountries=15

*Middle East, and North Africa Region
*Middle East and North Africa: 19 countries, Morocco, Algeria, Tunisia, Libya, Egypt, Israel, West Bank/Gaza, Lebanon, Syria, Jordan, Saudi Arabia, Yemen, Oman, UAE, Qatar, Kuwait, Iraq, Iran, Bahrain

gen region1023numcountries=19

*North America region_id
*4 countries: United States, Canada, Bermuda, Mexico
gen region1024numcountries=4

*North and Central America Region
*11 countries: United States, Canada, Bermuda, Mexico, Belize, Costa Rica, El Salvador, Guatemala, Honduras, Nicaragua, Panama
gen region1025numcountries=11

*North Africa Region
*5 countries: Egypt, Libya, Algeria, Tunisia, Morocco
gen region1026numcountries=5

*Oceania region
*18 countries: Australia, Cook Islands, Fiji, French Polynesia, Kiribati, Marshall Islands, Micronesia, Nauru, New Caledonia, New Zealand, Pacific Island Trust, Palau, Papua New Guinea, Samoa, Soloman Islands, Tonga, Tuvalu, Vanuatu 
gen region1027numcountries=18

*Southern Africa region
*5 countries: Botswana, Eswatini, Lesotho, Namibia, South Africa
gen region1028numcountries=5

*Southern Asia region
*8 countries: India, Pakistan, Afghanistan, Bangladesh, Nepal, Sri Lanka, Bhutan, Maldives

gen region1029numcountries=8

*South and Central Asia region
*13 countries: Afghanistan, Bangladesh, Bhutan, India, Kazakhstan, Kyrgyzstan, Maldives, Nepal, Pakistan, Sri Lanka, Tajikistan, Turkmenistan, Uzbekistan

gen region1030numcountries=13

*South East Asia region
*11 countries: Brunei, Cambodia, Timor-Leste, Indonesia, Laos, Malaysia, Myanmar, Phillipines, Singapore, Thailand, Vietnam
gen region1031numcountries=11

*South America region
*13 countries: Argentina, Bolivia, Brazil, Chile, Colombia, Ecuador, French Guiana, Guyana, Paraguay, Peru, Suriname, Uruguay, Venezuela

gen region1032numcountries=13

*Subsaharan africa region
*51 countries: Angola, Benin, Botswana, British Indian Ocean Territory, Burkina Faso, Burundi, Cabo Verde, Cameroon, Central African Republic, Chad, Comoros, Congo (Brazzaville), Congo (Kinshasa), Cote d'Ivoire, Djibouti, Equatorial Guinea, Eritrea, Eswatini, Ethiopia, Gabon, Gambia, Ghana, Guinea, Guinea-Bissau, Kenya, Lesotho, Liberia, Madagascar, Malawi, Mali, Mauritania, Mauritius, Mozambique, Namibia, Niger, Nigeria, Rwanda, Sao Tome and Principe, Senegal, Seychelles, Sierra Leone, Somalia, South Africa, South Sudan, Sudan, Tanzania, Togo, Uganda, Western (Spanish) Sahara, Zambia, Zimbabwe

gen region1033numcountries=51

*West Africa Region
*15 countries: Benin, Burkina Faso, Cabo Verde, Cote d'ivoire, Gambia, Guinea, Guinea-Bissau, Liberia, Mali, Mauritania, Niger, Nigeria, Senegal, Sierra Leone, Togo
gen region1034numcountries=15

*Western Europe
*18 countries: Austria, Belgium, Denmark, France, Germany, Greece, Iceland, Ireland, Italy, Luxembourg, Malta, Netherlands, Norway, Portugal, Spain, Sweden, Switzerland, United Kingdom 

gen region1036numcountries=18

*create portioned out variables, taking the amount given to a region and dividing by the number of countries.  This is the Finkel solution with the RSAID variables.

*regional DG USAID/countries

gen region1005dgsplit = region1005dg/region1005numcountries
gen region1006dgsplit = region1006dg/region1006numcountries
gen region1007dgsplit = region1007dg/region1007numcountries
gen region1008dgsplit = region1008dg/region1008numcountries
gen region1009dgsplit = region1009dg/region1009numcountries
gen region1010dgsplit = region1010dg/region1010numcountries
gen region1011dgsplit = region1011dg/region1011numcountries
gen region1012dgsplit = region1012dg/region1012numcountries
gen region1013dgsplit = region1013dg/region1013numcountries
gen region1014dgsplit = region1014dg/region1014numcountries
gen region1015dgsplit = region1015dg/region1015numcountries
gen region1016dgsplit = region1016dg/region1016numcountries
gen region1017dgsplit = region1017dg/region1017numcountries
gen region1018dgsplit = region1018dg/region1018numcountries
gen region1019dgsplit = region1019dg/region1019numcountries
gen region1020dgsplit = region1020dg/region1020numcountries
gen region1021dgsplit = region1021dg/region1021numcountries
gen region1022dgsplit = region1022dg/region1022numcountries
gen region1023dgsplit = region1023dg/region1023numcountries
gen region1024dgsplit = region1024dg/region1024numcountries
gen region1025dgsplit = region1025dg/region1025numcountries
gen region1026dgsplit = region1026dg/region1026numcountries
gen region1027dgsplit = region1027dg/region1027numcountries
gen region1028dgsplit = region1028dg/region1028numcountries
gen region1029dgsplit = region1029dg/region1029numcountries
gen region1030dgsplit = region1030dg/region1030numcountries
gen region1031dgsplit = region1031dg/region1031numcountries
gen region1032dgsplit = region1032dg/region1032numcountries
gen region1033dgsplit = region1033dg/region1033numcountries
gen region1034dgsplit = region1034dg/region1034numcountries
gen region1036dgsplit = region1036dg/region1036numcountries

*regional non-DG USAID

gen region1005nondgsplit = region1005nondg/region1005numcountries
gen region1006nondgsplit = region1006nondg/region1006numcountries
gen region1007nondgsplit = region1007nondg/region1007numcountries
gen region1008nondgsplit = region1008nondg/region1008numcountries
gen region1009nondgsplit = region1009nondg/region1009numcountries
gen region1010nondgsplit = region1010nondg/region1010numcountries
gen region1011nondgsplit = region1011nondg/region1011numcountries
gen region1012nondgsplit = region1012nondg/region1012numcountries
gen region1013nondgsplit = region1013nondg/region1013numcountries
gen region1014nondgsplit = region1014nondg/region1014numcountries
gen region1015nondgsplit = region1015nondg/region1015numcountries
gen region1016nondgsplit = region1016nondg/region1016numcountries
gen region1017nondgsplit = region1017nondg/region1017numcountries
gen region1018nondgsplit = region1018nondg/region1018numcountries
gen region1019nondgsplit = region1019nondg/region1019numcountries
gen region1020nondgsplit = region1020nondg/region1020numcountries
gen region1021nondgsplit = region1021nondg/region1021numcountries
gen region1022nondgsplit = region1022nondg/region1022numcountries
gen region1023nondgsplit = region1023nondg/region1023numcountries
gen region1024nondgsplit = region1024nondg/region1024numcountries
gen region1025nondgsplit = region1025nondg/region1025numcountries
gen region1026nondgsplit = region1026nondg/region1026numcountries
gen region1027nondgsplit = region1027nondg/region1027numcountries
gen region1028nondgsplit = region1028nondg/region1028numcountries
gen region1029nondgsplit = region1029nondg/region1029numcountries
gen region1030nondgsplit = region1030nondg/region1030numcountries
gen region1031nondgsplit = region1031nondg/region1031numcountries
gen region1032nondgsplit = region1032nondg/region1032numcountries
gen region1033nondgsplit = region1033nondg/region1033numcountries
gen region1034nondgsplit = region1034nondg/region1034numcountries
gen region1036nondgsplit = region1036nondg/region1036numcountries

*rename year variable in prep for merge

rename fiscal_year year

*fill values with maximum, in preparation to getting down to single year observations

*dg
by year: egen _region1005dgsplit= max(region1005dgsplit)
by year: egen _region1006dgsplit= max(region1006dgsplit)
by year: egen _region1007dgsplit= max(region1007dgsplit)
by year: egen _region1008dgsplit= max(region1008dgsplit)
by year: egen _region1009dgsplit= max(region1009dgsplit)
by year: egen _region1010dgsplit= max(region1010dgsplit)
by year: egen _region1011dgsplit= max(region1011dgsplit)
by year: egen _region1012dgsplit= max(region1012dgsplit)
by year: egen _region1013dgsplit= max(region1013dgsplit)
by year: egen _region1014dgsplit= max(region1014dgsplit)
by year: egen _region1015dgsplit= max(region1015dgsplit)
by year: egen _region1016dgsplit= max(region1016dgsplit)
by year: egen _region1017dgsplit= max(region1017dgsplit)
by year: egen _region1018dgsplit= max(region1018dgsplit)
by year: egen _region1019dgsplit= max(region1019dgsplit)
by year: egen _region1020dgsplit= max(region1020dgsplit)
by year: egen _region1021dgsplit= max(region1021dgsplit)
by year: egen _region1022dgsplit= max(region1022dgsplit)
by year: egen _region1023dgsplit= max(region1023dgsplit)
by year: egen _region1024dgsplit= max(region1024dgsplit)
by year: egen _region1025dgsplit= max(region1025dgsplit)
by year: egen _region1026dgsplit= max(region1026dgsplit)
by year: egen _region1027dgsplit= max(region1027dgsplit)
by year: egen _region1028dgsplit= max(region1028dgsplit)
by year: egen _region1029dgsplit= max(region1029dgsplit)
by year: egen _region1030dgsplit= max(region1030dgsplit)
by year: egen _region1031dgsplit= max(region1031dgsplit)
by year: egen _region1032dgsplit= max(region1032dgsplit)
by year: egen _region1033dgsplit= max(region1033dgsplit)
by year: egen _region1034dgsplit= max(region1034dgsplit)
by year: egen _region1036dgsplit= max(region1036dgsplit)

*nondg
by year: egen _region1005nondgsplit= max(region1005nondgsplit)
by year: egen _region1006nondgsplit= max(region1006nondgsplit)
by year: egen _region1007nondgsplit= max(region1007nondgsplit)
by year: egen _region1008nondgsplit= max(region1008nondgsplit)
by year: egen _region1009nondgsplit= max(region1009nondgsplit)
by year: egen _region1010nondgsplit= max(region1010nondgsplit)
by year: egen _region1011nondgsplit= max(region1011nondgsplit)
by year: egen _region1012nondgsplit= max(region1012nondgsplit)
by year: egen _region1013nondgsplit= max(region1013nondgsplit)
by year: egen _region1014nondgsplit= max(region1014nondgsplit)
by year: egen _region1015nondgsplit= max(region1015nondgsplit)
by year: egen _region1016nondgsplit= max(region1016nondgsplit)
by year: egen _region1017nondgsplit= max(region1017nondgsplit)
by year: egen _region1018nondgsplit= max(region1018nondgsplit)
by year: egen _region1019nondgsplit= max(region1019nondgsplit)
by year: egen _region1020nondgsplit= max(region1020nondgsplit)
by year: egen _region1021nondgsplit= max(region1021nondgsplit)
by year: egen _region1022nondgsplit= max(region1022nondgsplit)
by year: egen _region1023nondgsplit= max(region1023nondgsplit)
by year: egen _region1024nondgsplit= max(region1024nondgsplit)
by year: egen _region1025nondgsplit= max(region1025nondgsplit)
by year: egen _region1026nondgsplit= max(region1026nondgsplit)
by year: egen _region1027nondgsplit= max(region1027nondgsplit)
by year: egen _region1028nondgsplit= max(region1028nondgsplit)
by year: egen _region1029nondgsplit= max(region1029nondgsplit)
by year: egen _region1030nondgsplit= max(region1030nondgsplit)
by year: egen _region1031nondgsplit= max(region1031nondgsplit)
by year: egen _region1032nondgsplit= max(region1032nondgsplit)
by year: egen _region1033nondgsplit= max(region1033nondgsplit)
by year: egen _region1034nondgsplit= max(region1034nondgsplit)
by year: egen _region1036nondgsplit= max(region1036nondgsplit)


*generating missing values which are just 0s when there is no value for that region that year.

replace _region1005dgsplit=0 if _region1005dgsplit==.
replace _region1006dgsplit=0 if _region1006dgsplit==.
replace _region1007dgsplit=0 if _region1007dgsplit==.
replace _region1008dgsplit=0 if _region1008dgsplit==.
replace _region1009dgsplit=0 if _region1009dgsplit==.
replace _region1010dgsplit=0 if _region1010dgsplit==.
replace _region1011dgsplit=0 if _region1011dgsplit==.
replace _region1012dgsplit=0 if _region1012dgsplit==.
replace _region1013dgsplit=0 if _region1013dgsplit==.
replace _region1014dgsplit=0 if _region1014dgsplit==.
replace _region1015dgsplit=0 if _region1015dgsplit==.
replace _region1016dgsplit=0 if _region1016dgsplit==.
replace _region1017dgsplit=0 if _region1017dgsplit==.
replace _region1018dgsplit=0 if _region1018dgsplit==.
replace _region1019dgsplit=0 if _region1019dgsplit==.
replace _region1020dgsplit=0 if _region1020dgsplit==.
replace _region1021dgsplit=0 if _region1021dgsplit==.
replace _region1022dgsplit=0 if _region1022dgsplit==.
replace _region1023dgsplit=0 if _region1023dgsplit==.
replace _region1024dgsplit=0 if _region1024dgsplit==.
replace _region1025dgsplit=0 if _region1025dgsplit==.
replace _region1026dgsplit=0 if _region1026dgsplit==.
replace _region1027dgsplit=0 if _region1027dgsplit==.
replace _region1028dgsplit=0 if _region1028dgsplit==.
replace _region1029dgsplit=0 if _region1029dgsplit==.
replace _region1030dgsplit=0 if _region1030dgsplit==.
replace _region1031dgsplit=0 if _region1031dgsplit==.
replace _region1032dgsplit=0 if _region1032dgsplit==.
replace _region1033dgsplit=0 if _region1033dgsplit==.
replace _region1034dgsplit=0 if _region1034dgsplit==.
replace _region1036dgsplit=0 if _region1036dgsplit==.

replace _region1005nondgsplit=0 if _region1005nondgsplit==.
replace _region1006nondgsplit=0 if _region1006nondgsplit==.
replace _region1007nondgsplit=0 if _region1007nondgsplit==.
replace _region1008nondgsplit=0 if _region1008nondgsplit==.
replace _region1009nondgsplit=0 if _region1009nondgsplit==.
replace _region1010nondgsplit=0 if _region1010nondgsplit==.
replace _region1011nondgsplit=0 if _region1011nondgsplit==.
replace _region1012nondgsplit=0 if _region1012nondgsplit==.
replace _region1013nondgsplit=0 if _region1013nondgsplit==.
replace _region1014nondgsplit=0 if _region1014nondgsplit==.
replace _region1015nondgsplit=0 if _region1015nondgsplit==.
replace _region1016nondgsplit=0 if _region1016nondgsplit==.
replace _region1017nondgsplit=0 if _region1017nondgsplit==.
replace _region1018nondgsplit=0 if _region1018nondgsplit==.
replace _region1019nondgsplit=0 if _region1019nondgsplit==.
replace _region1020nondgsplit=0 if _region1020nondgsplit==.
replace _region1021nondgsplit=0 if _region1021nondgsplit==.
replace _region1022nondgsplit=0 if _region1022nondgsplit==.
replace _region1023nondgsplit=0 if _region1023nondgsplit==.
replace _region1024nondgsplit=0 if _region1024nondgsplit==.
replace _region1025nondgsplit=0 if _region1025nondgsplit==.
replace _region1026nondgsplit=0 if _region1026nondgsplit==.
replace _region1027nondgsplit=0 if _region1027nondgsplit==.
replace _region1028nondgsplit=0 if _region1028nondgsplit==.
replace _region1029nondgsplit=0 if _region1029nondgsplit==.
replace _region1030nondgsplit=0 if _region1030nondgsplit==.
replace _region1031nondgsplit=0 if _region1031nondgsplit==.
replace _region1032nondgsplit=0 if _region1032nondgsplit==.
replace _region1033nondgsplit=0 if _region1033nondgsplit==.
replace _region1034nondgsplit=0 if _region1034nondgsplit==.
replace _region1036nondgsplit=0 if _region1036nondgsplit==.

*generate year tag to drop all other observations
egen tag=tag(year)
keep if tag==1

*will need to go into the vdem file and generate which country belongs to each region and sum over regions

***Final cleanup

*keep only variables which are at year level with the totals/countries, which is our control variable.  We will need to generate variable values in the next merged file, with each country getting its own sum depending on the regions it is in.

keep year _region1005dgsplit _region1006dgsplit _region1007dgsplit _region1008dgsplit _region1009dgsplit _region1010dgsplit _region1011dgsplit _region1012dgsplit _region1013dgsplit _region1014dgsplit _region1015dgsplit _region1016dgsplit _region1017dgsplit _region1018dgsplit _region1019dgsplit _region1020dgsplit _region1021dgsplit _region1022dgsplit _region1023dgsplit _region1024dgsplit _region1025dgsplit _region1026dgsplit _region1027dgsplit _region1028dgsplit _region1029dgsplit _region1030dgsplit _region1031dgsplit _region1032dgsplit _region1033dgsplit _region1034dgsplit _region1036dgsplit _region1005nondgsplit _region1006nondgsplit _region1007nondgsplit _region1008nondgsplit _region1009nondgsplit _region1010nondgsplit _region1011nondgsplit _region1012nondgsplit _region1013nondgsplit _region1014nondgsplit _region1015nondgsplit _region1016nondgsplit _region1017nondgsplit _region1018nondgsplit _region1019nondgsplit _region1020nondgsplit _region1021nondgsplit _region1022nondgsplit _region1023nondgsplit _region1024nondgsplit _region1025nondgsplit _region1026nondgsplit _region1027nondgsplit _region1028nondgsplit _region1029nondgsplit _region1030nondgsplit _region1031nondgsplit _region1032nondgsplit _region1033nondgsplit _region1034nondgsplit _region1036nondgsplit

*save as a data file for merging into vdem.  Then calculate each country's portion.

save "USAID20012018regionsplit.dta", replace

*fin